IF OBJECT_ID('dbo.GetDeliverableCodes') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetDeliverableCodes
    IF OBJECT_ID('dbo.GetDeliverableCodes') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetDeliverableCodes >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetDeliverableCodes >>>'
END
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER OFF
go
-- =======================================================================
-- Procedure Name: dbo.GetDeliverableCodes
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 7/13/2007
--
-- Description: Returns list of Codes for Deliverable tables
--        Requires a parameter asking for Category, Type or Status
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetDeliverableCodes('TYPE')
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 7/13/07     KC        Created
-- 6/15/07     KC        Permissions
-- 8/24/07     KC        Remomved Descriptions starting with CSCR.
--
-- =======================================================================
CREATE PROCEDURE dbo.GetDeliverableCodes
     (
     @CodeType          VARCHAR(20) = NULL
     )
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     CodeName            VARCHAR             
     CodeID              VARCHAR
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT

     -- Check for wildcard
     IF RIGHT(RTRIM(@CodeType),1) != '%' SET @CodeType = @CodeType + '%'
     IF LEFT(RTRIM(@CodeType),1) != '%' SET @CodeType = '%' + @CodeType 

     -- Result Query    
     SELECT DISTINCT 
               code_desc AS CodeName,
               codeID
     FROM      InfoEdCodetab
     WHERE     (@CodeType IS NULL OR codeID LIKE @CodeType) 
				--AND  code_desc NOT LIKE 'CSCR%' 
     ORDER BY CodeName
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.GetDeliverableCodes') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetDeliverableCodes >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetDeliverableCodes >>>'
go
GRANT EXECUTE ON dbo.GetDeliverableCodes TO insight
go
GRANT EXECUTE ON dbo.GetDeliverableCodes TO InsightReport
go
